#!/usr/bin/env python
# coding:utf-8


from utils.mysqlutils import MysqlUtils
from datatabletransfer import DataTableTransfer
from fieldsmap import *
import logging


class BondsSecurities(DataTableTransfer):
    """
    证券基本信息表(TQ_OA_STCODE) --> 证券内码表(sec_basic_info)(债券)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select SECODE, COMPCODE,SENAME,SESNAME,SYMBOL,SETYPE,SESPELL,LISTSTATUS,EXCHANGE,CUR,SOURCE_ID,COME_SOURCE,
    LISTDATE,DELISTDATE,SEC_TYPE from ( 
    select a.SECODE,concat('FD',a.COMPCODE) as COMPCODE, a.SENAME, a.SESNAME,a.SYMBOL,a.SETYPE,a.SESPELL,
     a.LISTSTATUS,a.EXCHANGE,a.CUR,  a.SECODE as SOURCE_ID, 'zh-data' as COME_SOURCE,b.LISTDATE, 
     b.DELISTDATE, 1004002 as SEC_TYPE from TQ_OA_STCODE a inner join TQ_BD_BASICINFO b on a.SECODE=b.SECODE where 
     a.SETYPE IN('401','402','403','404','405','406','407','408','410','411','412','499') 
     union all 
     select a.SECODE,concat('FD',a.COMPCODE) as COMPCODE, a.SENAME,  a.SESNAME,a.SYMBOL,a.SETYPE,a.SESPELL,
     a.LISTSTATUS,a.EXCHANGE,a.CUR, a.SECODE as SOURCE_ID, 'zh-data' as COME_SOURCE,b.LISTDATE, 
     b.DELISTDATE, 1004002 as SEC_TYPE from TQ_OA_STCODE a left join TQ_BD_BASICINFO b on a.SECODE=b.SECODE where 
     a.SETYPE IN('413','1501', '909')) ta;''')
    QUERY_SQL_INCREASE = ('''select SECODE, COMPCODE,SENAME,SESNAME,SYMBOL,SETYPE,SESPELL,LISTSTATUS,EXCHANGE,CUR,SOURCE_ID,
    COME_SOURCE, LISTDATE,DELISTDATE,SEC_TYPE from ( 
    select a.SECODE,concat('FD',a.COMPCODE) as COMPCODE, a.SENAME, a.SESNAME,a.SYMBOL,a.SETYPE,a.SESPELL,
     a.LISTSTATUS,a.EXCHANGE,a.CUR, a.SECODE as SOURCE_ID, 'zh-data' as COME_SOURCE,b.LISTDATE, 
     b.DELISTDATE, 1004002 as SEC_TYPE from TQ_OA_STCODE a inner join TQ_BD_BASICINFO b on a.SECODE=b.SECODE where 
     a.SETYPE IN('401','402','403','404','405','406','407','408','410','411','412','499') 
     and a.ENTRYDATE > date_sub(now(), interval 1 day) 
     union all 
     select a.SECODE,concat('FD',a.COMPCODE) as COMPCODE, a.SENAME, a.SESNAME,a.SYMBOL,a.SETYPE,a.SESPELL,
     a.LISTSTATUS,a.EXCHANGE,a.CUR, a.SECODE as SOURCE_ID, 'zh-data' as COME_SOURCE,b.LISTDATE, 
     b.DELISTDATE, 1004002 as SEC_TYPE from TQ_OA_STCODE a left join TQ_BD_BASICINFO b on a.SECODE=b.SECODE where 
     a.SETYPE IN('413','1501', '909') and a.ENTRYDATE > date_sub(now(), interval 1 day)) ta;''')

    STATS_SQL = ('''select count(1) from TQ_OA_STCODE a inner join TQ_BD_BASICINFO b on a.SECODE=b.SECODE where 
     a.SETYPE IN('401','402','403','404','405','406','407','408','410','411','412','499','1501', '909');''')  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_OA_STCODE 
     where SETYPE IN('401','402','403','404','405','406','407','408','410','411','412','413','499','1501', '909')
     and ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'sec_basic_info'  # 待插入的表名
    SOURCE_ID = ('source_id', )  # 被关联表的主键, 源表的唯一索引
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    # ABC规则补充后缀(sec_small_type,security_type, 后缀)
    ABC_CODE = {
        (1004002040, 1003027): '.TJS',
        (1004002040, 1003028): '.SEE',
        (1004002040, 1003029): '.CST',
        (1004002040, 1003030): '.ZJE',
        (1004002040, 1003031): '.HNEE',
        (1004002040, 1003032): '.QLE',
        (1004002040, 1003033): '.LEE',
        (1004002040, 1003034): '.WHE',
        (1004002040, 1003035): '.QHE',
        (1004002040, 1003036): '.GEE',
        (1004002040, 1003037): '.SEEC',
        (1004002040, 1003038): '.GSE',
        (1004002040, 1003039): '.BJE',
        (1004002040, 1003040): '.JSE',
        (1004002040, 1003041): '.HXE',
        (1004002040, 1003042): '.GDE',
        (1004002040, 1003043): '.AHE',
        (1004002040, 1003044): '.GZEF',
        (1004002040, 1003045): '.XJE',
        (1004002040, 1003046): '.IOC',
        (1004012,): '.HK',
        (1004002028,): '.IB',
        (1004002029,): '.IB',
        (1004002039,): '.IB',
        (1004002045,): '.IB',
        (1004002046,): '.ABS',
    }

    MAP_FIELDS = BondsSecuritiesMap
    MAP_VALUES = BondsSecuritiesValueMap
    DATETIME = {
        'ipo_date': None,
        'end_date': None,
    }

    @classmethod
    def import_mysql_data(cls, table, query, flag):
        """
        导表
        :param table:
        :param query:
        :param flag:
        :return:
        """

        logging.info('The table {0} {1} running...'.format(table, flag))
        rows = cls.export_mysql(query=query)
        logging.info('The table {1} records is {0} items, export finished.'.format(len(rows), table))
        cls.upset_mysql(table, rows)
        logging.info('The table {1} records is {0} items, update finished.'.format(len(rows), table))

    @classmethod
    def write_mysql(cls, table, item, column_value, upset):
        """
        更新数据写入MySQL
        :param table:
        :param item:
        :param column_value:
        :param upset:
        :return:
        """

        # 不能存sec_uni_code=0的记录
        if item.has_key('sec_uni_code') and item['sec_uni_code'] == 0:
            # logging.warn('The record sec_uni_code not exists.')
            return

        # 将未上市等待上市的大智慧代码替换为ABC自编代码(N-->B0),如B00000001(转换规则：将大智慧N替换为B0)
        if item['security_type'] == 1003024 and str(item['abc_code']).startswith('N'):
            item['abc_code'] = 'B0{0}'.format(item['abc_code'][1:])

        # 对OTC和港债无后缀的需要按照ABC规则补充后缀
        if item['sec_small_type'] == 1004002040 and not item['abc_code']:
            item['abc_code'] = '{0}{1}'.format(item['sec_code'],
                                               cls.ABC_CODE.get((item['sec_small_type'], item['security_type']), ''))
        if item['sec_small_type'] in(1004012, 1004002028, 1004002029, 1004002039) and not item['abc_code']:
            item['abc_code'] = '{0}{1}'.format(item['sec_code'],
                                               cls.ABC_CODE.get((item['sec_small_type'], ), ''))

        conn = MysqlUtils.connect(cls.data_center)
        if upset and column_value:
            cursor = MysqlUtils.columns_update(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()),
                                               select_column=cls.COLUMN_ID, select_value=column_value)
            # logging.info('The record {0} {1} update finished.'.format(item['source_id'], item['abc_code']))
        else:
            cursor = MysqlUtils.columns_insert(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()))
            # logging.info('The record {0} {1} insert finished.'.format(item['source_id'], item['abc_code']))
        if cursor:
            cursor.close()
        conn.close()

    @classmethod
    def delete_records(cls):
        """
        逻辑剔除的上市债券上市前信息(临时方案)
        :return:
        """

        del_sql = ('''SELECT concat('FD', SECODE) source_id FROM TQ_OA_STCODE 
        WHERE SETYPE LIKE'4%' AND SETYPE  NOT IN('409','413') AND  NOT EXISTS
        (SELECT * FROM TQ_BD_BASICINFO WHERE TQ_OA_STCODE.`SECODE`=TQ_BD_BASICINFO.`SECODE`);''')
        update_sql = '''update sec_basic_info set status='9' where sec_type=1004002 and source_id in (%s);'''
        update_condition = ''

        conn = MysqlUtils.connect(conf=cls.funds)
        cursor = MysqlUtils.common_query(conn=conn, query=del_sql)
        if cursor and cursor.rowcount:
            logging.info('Total records: {0}'.format(cursor.rowcount))
            rows = cursor.fetchall()
            for index, row in enumerate(rows):
                update_condition = '''{0}'{1}','''.format(update_condition, row[0])
            cursor.close()
        conn.close()

        conn = MysqlUtils.connect(conf=cls.data_center)
        cursor = MysqlUtils.common_query(conn=conn, query=update_sql % update_condition[:-1])
        if cursor:
            logging.info('Delete total records: {0}'.format(cursor.rowcount))
            conn.commit()
            cursor.close()
        conn.close()


class BondsSecuritiesRepur(DataTableTransfer):
    """
    证券基本信息表(TQ_OA_STCODE) --> 证券内码表(sec_basic_info)(债券回购)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', COMPCODE) as COMPCODE,SENAME,SESNAME,SYMBOL,
    case when EXCHANGE='001002' then concat(SYMBOL,'.SH') when EXCHANGE='001003' then concat(SYMBOL,'.SZ') 
    when EXCHANGE='001005' then concat(SYMBOL,'.IB') when EXCHANGE='001006' then concat(SYMBOL,'.FI') else '' end 
    as ABC_CODE,1004007 as SEC_TYPE,SETYPE,SESPELL,LISTSTATUS,EXCHANGE,BEGINDATE,ENDDATE,CUR,
    'zh-data' as COME_SOURCE, SECODE as SOURCE_ID from TQ_OA_STCODE 
    where SETYPE in(811,812) and EXCHANGE in('001002','001003','001005','001006') limit %s, 1000;''')
    QUERY_SQL_INCREASE = ('''select concat('FD', COMPCODE) as COMPCODE,SENAME,SESNAME,SYMBOL,
    case when EXCHANGE='001002' then concat(SYMBOL,'.SH') when EXCHANGE='001003' then concat(SYMBOL,'.SZ') 
    when EXCHANGE='001005' then concat(SYMBOL,'.IB') when EXCHANGE='001006' then concat(SYMBOL,'.FI') else '' end 
    as ABC_CODE,1004007 as SEC_TYPE,SETYPE,SESPELL,LISTSTATUS,EXCHANGE,BEGINDATE,ENDDATE,CUR,
    'zh-data' as COME_SOURCE, SECODE as SOURCE_ID from TQ_OA_STCODE 
    where SETYPE in(811,812) and EXCHANGEin('001002','001003','001005','001006') 
    and ENTRYDATE > date_sub(now(), interval 1 day);''')

    STATS_SQL = ('''select count(1) from TQ_OA_STCODE 
    where SETYPE in(811,812) and EXCHANGE in('001002','001003','001005','001006');''')  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_OA_STCODE 
     where SETYPE in(811,812) and EXCHANGE in('001002','001003','001005','001006') 
     and ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'sec_basic_info'  # 待插入的表名
    SOURCE_ID = ('source_id', )  # 被关联表的主键, 源表的唯一索引
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    MAP_FIELDS = BondsSecuritiesRepurMap
    MAP_VALUES = BondsSecuritiesRepurValueMap
    DATETIME = {
        'ipo_date': None,
        'end_date': None,
    }


if __name__ == '__main__':

    log_path = '../logs/export_bonds_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # BondsSecurities.work()
    # BondsSecurities.delete_records()
    BondsSecuritiesRepur.work()
